﻿using System;
using System.Data.Odbc;
using System.Data;

namespace Gencode.Common.SQL
{
	public class Querry
	{
		OdbcConnection m_odbcConnection = null;
		OdbcCommand m_command;
        System.Collections.Generic.Dictionary<string, OdbcParameter> parameterList;

		string m_commandString;
		int m_parameterCount = 0;
		string m_stpName;
        string m_loggstring;

		static bool silentTransaction = false;
		/// <summary>
		/// will show a messagebox if an error occured if true
		/// </summary>
		public static bool SilentTransaction
		{
			get
			{
				return silentTransaction;
			}
			set
			{
				silentTransaction = value;
			}
		}

		public Querry()
		{
			CreateCommandObject();
		}

		public Querry(string STPName)
		{
			CreateCommandObject();
			SetSTPName(STPName);
		}

		public void SetSTPName(string stpName)
		{
			this.m_stpName = stpName;
		}

		private void CreateCommandObject()
		{
			this.m_command = new OdbcCommand();
			m_command.CommandType = CommandType.StoredProcedure;
		}

        /// <summary>
		/// Adds a Parameter to the SQLString
		/// </summary>
		/// <param name="parameterName">Name of Parameter</param>
		/// <param name="value">Value of Parameter</param>
        public void AddParameter(string parameterName, int value)
        {
            AddParameter(parameterName, value, ParameterDirection.Input);
        }

        /// <summary>
        /// Adds a Parameter to the SQLString
        /// </summary>
        /// <param name="parameterName">Name of Parameter</param>
        /// <param name="value">Value of Parameter</param>
        public void AddParameter(string parameterName, DateTime value)
        {
            AddParameter(parameterName, value.ToString("yyyyMMdd"), ParameterDirection.Input);
        }

		/// <summary>
		/// Adds a Parameter to the SQLString
		/// </summary>
		/// <param name="parameterName">Name of Parameter</param>
		/// <param name="value">Value of Parameter</param>
        public void AddParameter(string parameterName, int value, ParameterDirection direction)
		{
			// parameterName muss immer mit @ anfangen z.B. "@anstellungid"
			if (this.m_parameterCount == 0)
			{
				this.InitializeCommandString();
                m_loggstring = m_stpName + " " + value.ToString();
			}
			else
			{
				this.AddParameterToCmdString();
                m_loggstring += ", " + value.ToString();
			}

			OdbcParameter prm = new OdbcParameter(parameterName, OdbcType.Int);//"@anstellungid"
			prm.Direction = ParameterDirection.Input;
			prm.Value = Convert.ToString(value);
            prm.Direction = direction;
			m_command.Parameters.Add(prm);
            
			m_parameterCount++;
		}

        /// <summary>
		/// Adds a Parameter to the SQLString
		/// </summary>
		/// <param name="parameterName">Name of Parameter</param>
		/// <param name="value">Value of Parameter</param>
        public void AddParameter(string parameterName, string value)
        {
            AddParameter(parameterName, value, ParameterDirection.Input);
        }

		/// <summary>
		/// Adds a Parameter to the SQLString
		/// </summary>
		/// <param name="parameterName">Name of Parameter</param>
		/// <param name="value">Value of Parameter</param>
		public void AddParameter(string parameterName, string value, ParameterDirection direction)
		{
			// parameterName muss immer mit @ anfangen z.B. "@anstellungid"
			if (this.m_parameterCount == 0)
			{
				this.InitializeCommandString();
                m_loggstring = m_stpName + " " + value;
			}
			else
			{
				this.AddParameterToCmdString();
                m_loggstring += ", " + value.ToString();
			}

			OdbcParameter prm = new OdbcParameter(parameterName, OdbcType.VarChar);//"@anstellungid"
			prm.Direction = ParameterDirection.Input; //.Output;
			prm.Value = Convert.ToString(value);
            prm.Direction = direction;
			this.m_command.Parameters.Add(prm);

			this.m_parameterCount++;
		}

        /// <summary>
		/// Adds a Parameter to the SQLString
		/// </summary>
		/// <param name="parameterName">Name of Parameter</param>
		/// <param name="value">Value of Parameter</param>
        public void AddParameter(string parameterName, double value)
        {
            AddParameter(parameterName, value, ParameterDirection.Input);
        }

		/// <summary>
		/// Adds a Parameter to the SQLString
		/// </summary>
		/// <param name="parameterName">Name of Parameter</param>
		/// <param name="value">Value of Parameter</param>
        public void AddParameter(string parameterName, double value, ParameterDirection direction)
		{
			// parameterName muss immer mit @ anfangen z.B. "@anstellungid"
			if (this.m_parameterCount == 0)
			{
				this.InitializeCommandString();
                m_loggstring = m_stpName + " " + value.ToString();
			}
			else
			{
				this.AddParameterToCmdString();
                m_loggstring += ", " + value.ToString();
			}

			OdbcParameter prm = new OdbcParameter(parameterName, OdbcType.Double);
			prm.Direction = ParameterDirection.Input;
			prm.Value = Convert.ToString(value);
            prm.Direction = direction;
			this.m_command.Parameters.Add(prm);

			this.m_parameterCount++;
		}

		private void AddParameterToCmdString()
		{
			this.m_commandString = m_commandString + ", ?";
		}

		private void InitializeCommandString()
		{
			this.m_commandString = "{call " + this.m_stpName + " (?";
		}


        public object GetReturnParameter(string parameterName)
        {
            if (parameterList.ContainsKey(parameterName))
                return parameterList[parameterName].Value;

            return null;
        }

		private void FinishCommandObject()
		{
			this.m_commandString = this.m_commandString + ")}";
			m_command.CommandText = this.m_commandString;
			m_command.Connection = ODBCConnection;
		}

		/// <summary>
		/// Executes the Stored Procedure to the Database
		/// </summary>
		/// <returns>True if an error occured</returns>
		public bool Execute()
		{
			FinishCommandObject();
			bool causedError = false;

			try
			{
				ODBCConnection.Open();
				m_command.ExecuteNonQuery();
			}
			catch (OdbcException objError)
			{
				Gencode.Common.Util.ErrorHandler.LoggError("STP execute error at " + m_stpName, "Querry.Execute()", objError.Message.ToString());
				if (!silentTransaction)
					Gencode.Common.Util.ErrorHandler.ShowError("Es ist ein Fehler beim ausführen in Prozedur " + m_stpName + " aufgetreten\n" + objError.Message.ToString(), "Fehler");
				causedError = true;
			}
			finally
			{
				ODBCConnection.Close();
			}
			return causedError;
		}

        /// <summary>
        /// Executes the Stored Procedure to the Database
        /// </summary>
        /// <param name="loggMessage">log message?</param>
        /// <returns>True if an error occured</returns>
        public bool Execute(bool loggMessage)
        {
            if (loggMessage)
                Gencode.Common.ActionLogger.Logger.LoggMessage(Gencode.Common.ActionLogger.MessageType.SQL, m_loggstring);

            return Execute();
        }

		/// <summary>
		/// Executes the Procedure and returns all values in a DataTable
		/// </summary>
		/// <param name="loggMessage">Make an entry in the logfile</param>
		/// <returns>DataTable with all data from the Stored Procedure</returns>
		public DataTable ExecuteForDataTable(bool loggMessage = false)
		{
			if (loggMessage)
				Gencode.Common.ActionLogger.Logger.LoggMessage(Gencode.Common.ActionLogger.MessageType.SQL, m_loggstring);

			OdbcDataReader reader;
			DataTable dt = new DataTable();
			this.FinishCommandObject();

			try
			{
				ODBCConnection.Open();

				reader = m_command.ExecuteReader();

                DataReaderAdapter dra = new DataReaderAdapter();
                dra.FillFromReader(dt, reader);
                //dv = new DataView(dt);
			}
			catch (Exception objError)
			{
				//write error to the windows event log
				//WriteToEventLog(objError);
				//throw;

				//Util.LastErrorText = objError.Message;
				if (Util.ErrorHandler != null)
				{
					Util.ErrorHandler.ShowError(objError.Message);
				}

				Gencode.Common.Util.ErrorHandler.LoggError("STP execute error at " + m_stpName, "Querry.ExecuteForDataTable()", objError.Message.ToString());
				Gencode.Common.Util.ErrorHandler.ShowError("Es ist ein Fehler beim ausführen in Prozedur " + m_stpName + " aufgetreten\n" + objError.Message.ToString(), "Fehler");
			}
			finally
			{
				ODBCConnection.Close();
			}
			return dt;
		}

        /// <summary>
        /// Executes the Procedure and returns all values in a DataView
        /// </summary>
        /// <returns>DataView with all data from the Stored Procedure</returns>
        public DataView ExecuteForDataView()
        {
            DataView dv;
            dv = new DataView(ExecuteForDataTable());
            
            return dv;
        }

        /// <summary>
        /// Executes the Procedure and returns all values in a DataView
        /// </summary>
        /// <returns>DataView with all data from the Stored Procedure</returns>
        public DataView ExecuteForDataView(bool loggMessage)
        {
            if (loggMessage)
                Gencode.Common.ActionLogger.Logger.LoggMessage(Gencode.Common.ActionLogger.MessageType.SQL, m_loggstring);

            return ExecuteForDataView();
        }

		#region Properties
		/// <summary>
		/// OdbcConnection object
		/// </summary>
		public OdbcConnection ODBCConnection
		{
			get
			{
				if (m_odbcConnection == null)
					return DatabaseConection.ODBCConnection;

				return m_odbcConnection;
			}
			set
			{
				m_odbcConnection = value;
			}
		}
		#endregion
	}
}
